
WITH Cte AS (
SELECT t.name  AS TableName, t.[object_id], SUM ( CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END ) 
       AS Primarykey, SUM ( CASE WHEN i.[type] = 1 THEN 1 ELSE 0 END ) AS 
       ClusteredIdx, SUM ( CASE WHEN i.[type] = 2 THEN 1 ELSE 0 END ) AS 
       NonClusteredIdx, SUM ( CASE WHEN i.[type] = 0 THEN 1 ELSE 0 END ) AS HeapIdx, COUNT ( * ) 
       TotalIdxpertable, SUM(
           ISNULL ( sta.range_scan_count + sta.singleton_lookup_count, 0 )
       )       AS 'No of Time Accessed by user untill SERVER started'
FROM   sys.tables t
       LEFT OUTER JOIN sys.indexes i
            ON  t.[object_id] = i.[object_id]
       LEFT OUTER JOIN Sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL ) 
            sta
            ON  t.[object_id] = sta.[object_id] AND
                sta.index_id = i.index_id
GROUP BY
       t.name, t.[object_id]
)

SELECT TableName, Primarykey, ClusteredIdx, NonClusteredIdx, HeapIdx, TotalIdxpertable, 
       [No of Time Accessed by user untill SERVER started]
FROM   cte
ORDER BY NonClusteredIdx desc
       

GO

SELECT 'TOTAL NUMBER OF ' + o.type_desc AS Details, COUNT ( * ) AS Total
FROM   sys.objects o
GROUP BY
       o.type_desc
ORDER BY
       o.type_desc
GO